package com.englishtest.city.dao;

import java.util.Collections;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.englishtest.base.GlobalConstant;
import com.englishtest.base.Paginater;
import com.englishtest.base.db.HqlUtils;

public class CityDao extends HibernateDaoSupport {
	
	/**
	 * Search the city by keyword and valid property
	 * @param searchWord
	 * @param isValid
	 * @param paginater
	 * @return
	 */
	public List searchCity(String searchWord, int isValid, Paginater paginater) {
		StringBuffer cond = new StringBuffer();
		if(!StringUtils.isBlank(searchWord)) {
			cond.append(" (city.cityCode like :searchWord or city.cityName like :searchWord) ");
		}
		if(isValid != GlobalConstant.BOOLEAN_ALL) {
			HqlUtils.appendAnd(cond);
			cond.append(" city.ifValid=").append(isValid == GlobalConstant.BOOLEAN_YES ? "true " : "false ");
		}
		HqlUtils.insertWhere(cond);
		
		List resultList = Collections.EMPTY_LIST;

        Session session = getSession();
        if(paginater != null && paginater.isNeedTotalCount()) {
            Query query = session.createQuery("select count(city) from City city " + cond);
            if(!StringUtils.isBlank(searchWord)) {
            	query.setString("searchWord", "%" + searchWord + "%");
            }
            paginater.setTotalCount(((Long) query.iterate().next()).intValue());
        }

        if(paginater == null || paginater.getTotalCount() > 0) {
            Query query = session.createQuery("from City city " + cond);
            if(!StringUtils.isBlank(searchWord)) {
            	query.setString("searchWord", "%" + searchWord + "%");
            }
            HqlUtils.setPaginaterFirstResultAndMaxResults(query, paginater);
            resultList = query.list();
        }
        return resultList;
	}
	
	/**
	 * Search the college by query
	 * @param searchWord
	 * @param paginater
	 * @return
	 */
	public List searchCollege(String searchWord, Paginater paginater) {
		StringBuffer cond = new StringBuffer();
		if(!StringUtils.isBlank(searchWord)) {
			cond.append(" (college.collegeCode like :searchWord or college.collegeName like :searchWord) ");
		}
		HqlUtils.insertWhere(cond);
		
		List resultList = Collections.EMPTY_LIST;

        Session session = getSession();
        if(paginater != null && paginater.isNeedTotalCount()) {
            Query query = session.createQuery("select count(college) from College college " + cond);
            if(!StringUtils.isBlank(searchWord)) {
            	query.setString("searchWord", "%" + searchWord + "%");
            }
            paginater.setTotalCount(((Long) query.iterate().next()).intValue());
        }

        if(paginater == null || paginater.getTotalCount() > 0) {
            Query query = session.createQuery("from College college " + cond);
            if(!StringUtils.isBlank(searchWord)) {
            	query.setString("searchWord", "%" + searchWord + "%");
            }
            HqlUtils.setPaginaterFirstResultAndMaxResults(query, paginater);
            resultList = query.list();
        }
        return resultList;
	}
	
	public List searchCollegeField(String searchWord, int isValid, Paginater paginater) {
		StringBuffer cond = new StringBuffer();
		if(!StringUtils.isBlank(searchWord)) {
			cond.append(" (fieldtime.ftName like :searchWord or fieldtime.ftCode like :searchWord) ");
		}
		if(isValid != GlobalConstant.BOOLEAN_ALL) {
			HqlUtils.appendAnd(cond);
			cond.append(" fieldtime.ifValid=").append(isValid == GlobalConstant.BOOLEAN_YES ? "true " : "false ");
		}
		HqlUtils.insertWhere(cond);
		
		List resultList = Collections.EMPTY_LIST;

        Session session = getSession();
        if(paginater != null && paginater.isNeedTotalCount()) {
            Query query = session.createQuery("select count(fieldtime) from Fieldtime fieldtime " + cond);
            if(!StringUtils.isBlank(searchWord)) {
            	query.setString("searchWord", "%" + searchWord + "%");
            }
            paginater.setTotalCount(((Long) query.iterate().next()).intValue());
        }

        if(paginater == null || paginater.getTotalCount() > 0) {
            Query query = session.createQuery("from Fieldtime fieldtime " + cond);
            if(!StringUtils.isBlank(searchWord)) {
            	query.setString("searchWord", "%" + searchWord + "%");
            }
            HqlUtils.setPaginaterFirstResultAndMaxResults(query, paginater);
            resultList = query.list();
        }
        return resultList;
	}
}
